/**
 * 该文件用来配置前端sql查询语句
 */

const sqlMap = {
  user: {
    login: `SELECT id,name,u_img,sex,ccount,token FROM users WHERE pass=? and ccount=?`,
    find: `SELECT ccount FROM users WHERE ccount=?`,
    add:  `INSERT INTO users (ccount,name,pass,sex,u_img,add_time, token) VALUES (?,?,?,?,?,?,?)`,
    vertoken: `SELECT token FROM users WHERE token=?`,
    update1: `UPDATE users SET name=?,sex=?,u_img=? WHERE id=?`,
    get_num: `
      SELECT u.id, u.name, u.u_img, u.ccount,
      (SELECT COUNT(*) FROM comments c WHERE c.u_id = u.id) as comments_num,
      (SELECT COUNT(*) FROM dot d WHERE d.u_id=u.id) as dot_num
      FROM users u 
      WHERE u.id=?
    `,
    up_pwd: `UPDATE users SET pass=? WHERE pass=? and id=?`
  },
  message: {
    find: `
      SELECT m.id,m.content,m.type,m.add_time,m.u_id as m_uid,u.ccount,u.u_img,
      (SELECT COUNT(*) FROM comments c WHERE c.m_id = m.id) as comments_num,
      (SELECT COUNT(*) FROM dot d WHERE d.m_id = m.id) as dot_num, 
      (SELECT COUNT(*) FROM message) as count
      FROM message m 
      LEFT JOIN users u ON m.u_id = u.id
      limit ? offset ?
    `,
    find1: `
      SELECT m.id,m.content,m.type,m.add_time,m.u_id as m_uid,u.ccount,u.u_img,
      (SELECT COUNT(*) FROM comments c WHERE c.m_id = m.id) as comments_num,
      (SELECT COUNT(*) FROM dot d WHERE d.m_id = m.id) as dot_num, 
      (SELECT COUNT(*) FROM message) as count
      FROM message m 
      LEFT JOIN users u ON m.u_id = u.id
      WHERE m.u_id=?
      limit ? offset ?
    `,
    mess_det: `
      SELECT m.id,m.content,m.type,m.add_time,m.u_id as m_uid,u.ccount,u.u_img,u.name as u_name,
      (SELECT COUNT(*) FROM comments c WHERE c.m_id = m.id) as comments_num,
      (SELECT COUNT(*) FROM dot d WHERE d.m_id = m.id) as dot_num,
      (SELECT COUNT(*) FROM dot d WHERE (d.u_id=? and d.m_id=?)) as hasdot
      FROM message m 
      LEFT JOIN users u ON m.u_id = u.id
      WHERE m.id=?
    `,
    add: `INSERT INTO message (content,u_id,type,add_time) VALUES (?,?,?,?)`,
    remove: `
      DELETE message,comments,dot
      FROM message 
      LEFT JOIN comments ON message.id=comments.m_id
      LEFT JOIN dot ON message.id=dot.m_id
      WHERE message.id=?
    `
  },
  dot: {
    find: `SELECT * FROM dot WHERE u_id=? and m_id=?`,
    add: 'INSERT INTO dot (u_id,m_id) VALUES (?,?)',
    remove: `DELETE FROM dot WHERE u_id=? and m_id=?`
  },
  comments: {
    find: `
      SELECT c.id, c.m_id as m_id, c.u_id as u_id, c.content, u.ccount, u.name as u_name, u.u_img, c.add_time
      FROM comments c 
      LEFT JOIN message m ON c.m_id = m.id 
      LEFT JOIN users u ON c.u_id = u.id 
      WHERE c.m_id=?
      order by c.id desc
    `,
    add: `INSERT INTO comments (u_id,m_id,content,add_time) VALUES (?,?,?,?)`,
    remove: `DELETE FROM comments WHERE id=?`
  },
};

module.exports = sqlMap;